﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Collections;
using System.Data.OracleClient;
using System.Data;

namespace DBSyn
{
    public abstract class DbHelperSQL
    {
        /// <summary>
        /// 卡物流系统连接字符串
        /// </summary>
        public static readonly string Con_CardLog = ConfigurationManager.AppSettings["OraConn_CardLog"].ToString();
        /// <summary>
        /// 一级平台链接字符串
        /// </summary>
        public static readonly string Con_JY = ConfigurationManager.AppSettings["OraConn_JY"].ToString();

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static int ExecuteSqlTran(List<String> SQLStringList,bool IsYJ)
        {
            int val = 0;
            string strCon = Con_CardLog;
            if (IsYJ)
                strCon = Con_JY;
            using (OracleConnection conn = new OracleConnection(strCon))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string sql in SQLStringList)
                    {
                        if (!String.IsNullOrEmpty(sql))
                        {
                            cmd.CommandText = sql;
                            val += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OracleClient.OracleException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            return val;
        }


        /// <summary>
        /// 根据SQL语句查询返回结果的DataTable
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回查询结果的DataTable</returns>
        public static DataTable GetQueryResult(string sql, bool IsYJ)
        {
            string strCon = Con_CardLog;
            if (IsYJ)
                strCon = Con_JY;

            DataTable tbl = new DataTable();
            OracleConnection conn = null;
            conn = new OracleConnection(strCon);
            using (OracleDataAdapter adapter = new OracleDataAdapter(sql, conn))
            {
                adapter.Fill(tbl);
            }
            return tbl;
        }
    }
}